package com.zx.oa.dao.marketStudent;

import com.zx.oa.dao.BaseDao;
import com.zx.oa.entity.MarketStudent;
import com.zx.oa.entity.ShowMarketStudent;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MarketStudentDaoImpl extends BaseDao implements MarketStudentDao {

    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    @Override
    public List<ShowMarketStudent> getStudentAll(String name, String className, String stuType, String intention, int currentPageNo, int pageSize) {
        List<ShowMarketStudent> marketStudentList=new ArrayList<>();
        //可变长度字符串
        StringBuffer sb=new StringBuffer("SELECT ms.id,ms.studentName,mc.className,ms.fromschool,ms.education \n" +
                "FROM marketstudent ms,marketclass mc\n" +
                "WHERE ms.mkClassId=mc.mkClassId");
        conn=this.getConnection();
        //按照顺序给？拼接参数的值
        List<Object> params=new ArrayList<>();
        if(null!=name&&!"".equals(name)){
            sb.append(" AND  ms.`studentName` LIKE ?");
            params.add("%"+name+"%");
        }
        if(null!=className&&!"".equals(className)){
            sb.append(" AND  mc.`className` LIKE ?");
            params.add("%"+className+"%");
        }
        if(null!=stuType&&!"".equals(stuType)){
            sb.append(" AND  mc.`classType` LIKE ?");
            params.add("%"+stuType+"%");
        }
        if(null!=intention&&!"".equals(intention)){
            sb.append(" AND  ms.`willtrain` LIKE ?");
            params.add("%"+intention+"%");
        }
        sb.append(" LIMIT ?,?");
        params.add((currentPageNo-1)*pageSize);
        params.add(pageSize);
        try {
            pstmt = conn.prepareStatement(sb.toString());
            //依次给?参数赋值
            for(int i=0;i<params.size();i++){
                pstmt.setObject((i+1),params.get(i));
            }
            rs = pstmt.executeQuery();
            while(rs.next()){
                Integer id=rs.getInt(1);
                String stuName=rs.getString(2);
                String claName=rs.getString(3);
                String fromSchool=rs.getString(4);
                String education=rs.getString(5);
                ShowMarketStudent showMarketStudent=new ShowMarketStudent(id,stuName,claName,fromSchool,education);
                marketStudentList.add(showMarketStudent);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeAll(conn,pstmt,rs);
        }
        return marketStudentList;
    }

    @Override
    public int getStudentTotalCount(String name, String className, String stuType, String intention) {
        int count=-1;//总记录数
        //可变长度字符串
        StringBuffer sb = new StringBuffer("SELECT COUNT(*) FROM marketstudent ms,marketclass mc\n" +
                "WHERE ms.mkClassId=mc.mkClassId");
        conn = this.getConnection();
        //按照顺序给？拼接参数的值
        List<Object>params=new ArrayList<>();
        if(null!=name&&!"".equals(name)){
            sb.append(" AND  ms.`studentName` LIKE ?");
            params.add("%"+name+"%");
        }
        if(null!=className&&!"".equals(className)){
            sb.append(" AND  mc.`className` LIKE ?");
            params.add("%"+className+"%");
        }
        if(null!=stuType&&!"".equals(stuType)){
            sb.append(" AND  mc.`classType` LIKE ?");
            params.add("%"+stuType+"%");
        }
        if(null!=intention&&!"".equals(intention)){
            sb.append(" AND  ms.`willtrain` LIKE ?");
            params.add("%"+intention+"%");
        }
        try {
            pstmt = conn.prepareStatement(sb.toString());
            //依次给?参数赋值
            for(int i=0;i<params.size();i++){
                pstmt.setObject((i+1),params.get(i));
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                count=rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeAll(conn,pstmt,rs);
        }
        return count;
    }

    @Override
    public List<String> getIntentionAll() {
        List<String> list=new ArrayList<>();
        conn=this.getConnection();
        String sql="SELECT DISTINCT willtrain  FROM marketstudent ms";
        try {
            pstmt=conn.prepareStatement(sql);
            rs=pstmt.executeQuery();
            while (rs.next()){
                String intention=rs.getString(1);
                list.add(intention);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeAll(conn,pstmt,rs);
        }

        return list;
    }

    @Override
    public List<ShowMarketStudent> getStudentAll(String name, String className, String stuType, String intention) {
        List<ShowMarketStudent> marketStudentList=new ArrayList<>();
        //可变长度字符串
        StringBuffer sb=new StringBuffer("SELECT ms.id,ms.studentName,mc.className,ms.fromschool,ms.education \n" +
                "FROM marketstudent ms,marketclass mc\n" +
                "WHERE ms.mkClassId=mc.mkClassId");
        conn=this.getConnection();
        //按照顺序给？拼接参数的值
        List<Object> params=new ArrayList<>();
        if(null!=name&&!"".equals(name)){
            sb.append(" AND  ms.`studentName` LIKE ?");
            params.add("%"+name+"%");
        }
        if(null!=className&&!"".equals(className)){
            sb.append(" AND  mc.`className` LIKE ?");
            params.add("%"+className+"%");
        }
        if(null!=stuType&&!"".equals(stuType)){
            sb.append(" AND  mc.`classType` LIKE ?");
            params.add("%"+stuType+"%");
        }
        if(null!=intention&&!"".equals(intention)){
            sb.append(" AND  ms.`willtrain` LIKE ?");
            params.add("%"+intention+"%");
        }
        try {
            pstmt = conn.prepareStatement(sb.toString());
            //依次给?参数赋值
            for(int i=0;i<params.size();i++){
                pstmt.setObject((i+1),params.get(i));
            }
            rs = pstmt.executeQuery();
            while(rs.next()){
                Integer id=rs.getInt(1);
                String stuName=rs.getString(2);
                String claName=rs.getString(3);
                String fromSchool=rs.getString(4);
                String education=rs.getString(5);
                ShowMarketStudent showMarketStudent=new ShowMarketStudent(id,stuName,claName,fromSchool,education);
                marketStudentList.add(showMarketStudent);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeAll(conn,pstmt,rs);
        }
        return marketStudentList;
    }

    /**
     * 获取所有性格
     * @return
     */
    @Override
    public List<String> getCharacter() {
        List<String> list=new ArrayList<>();
        conn=this.getConnection();
        String sql="SELECT DISTINCT xinge FROM marketstudent";
        try {
            pstmt=conn.prepareStatement(sql);
            rs=pstmt.executeQuery();
            while (rs.next()){
                list.add(rs.getString(1));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    /**
     * 增加学生
     * @param id
     * @param name
     * @param className
     * @param fromSchool
     * @param education
     * @param phone
     * @param qq
     * @param character
     * @param remarks
     * @param intention
     * @param createTime
     * @param createName
     * @return
     */
    @Override
    public boolean addMarketStudent(Integer id, String name, String className, String fromSchool, String education, Integer phone, Integer qq, String character, String remarks, String intention, String createTime, String createName) {
        String sql="INSERT INTO marketstudent(studentName,mkClassId,fromschool,education,phone,qq,xinge,beizhu,willtrain,createTime,createauthorID)\n" +
                "VALUES(?,(SELECT mkClassId FROM marketclass WHERE className=?),?,?,?,?,?,?,?,NOW(),?)";

        Object [] params={
                name,
                className,
                fromSchool,
                education,
                phone,
                qq,
                character,
                remarks,
                intention,
                createName
        };

        if(this.executeUpdateInfo(sql,params)>0){
            return  true;
        }
        return false;
    }

    @Override
    public boolean delMarketStudentById(Integer id) {
        String sql="DELETE FROM marketstudent WHERE id=?";

        Object [] params={id};

        if(this.executeUpdateInfo(sql,params)>0){
            return  true;
        }

        return false;
    }

    @Override
    public MarketStudent getStudentById(Integer id) {
        MarketStudent marketStudent=null;
        conn=this.getConnection();
        String sql="SELECT * FROM marketstudent WHERE id=?";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            rs=pstmt.executeQuery();
            while (rs.next()){
                Integer stuId=rs.getInt(1);
                String name=rs.getString(2);
                Integer mkClaId=rs.getInt(3);
                String fromSchool=rs.getString(4);
                String education=rs.getString(5);
                String phone=rs.getString(6);
                String qq=rs.getString(7);
                String character=rs.getString(8);
                String remarks=rs.getString(9);
                String intention=rs.getString(10);
                String createTime=rs.getString(11);
                String createName=rs.getString(12);
                String modifyName=rs.getString(13);
                String modifyTime=rs.getString(14);
                marketStudent=new MarketStudent(stuId,name,mkClaId,fromSchool,education,phone,qq,character,remarks,intention,createTime,createName,modifyName,modifyTime);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return marketStudent;
    }

    @Override
    public boolean modifyMarketStudent(MarketStudent marketStudent, String createName) {
        String sql="UPDATE marketstudent SET studentName=?,mkClassId=?,fromSchool=?,education=?,phone=?,qq=?,xinge=?,beizhu=?,willtrain=?,modifyauthor=?,modifyauthorTime=(SELECT NOW()) WHERE id=?";

        Object [] params={
                marketStudent.getStudentName(),
                marketStudent.getMkClassId(),
                marketStudent.getFromSchool(),
                marketStudent.getEducation(),
                marketStudent.getPhone(),
                marketStudent.getQq(),
                marketStudent.getXinGe(),
                marketStudent.getBeiZhu(),
                marketStudent.getWillTrain(),
                createName,
                marketStudent.getId()
        };

        if(this.executeUpdateInfo(sql,params)>0){
            return  true;
        }

        return false;
    }
}
